﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using DBUtil;
using System.Collections;

namespace WebWcf
{
    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码、svc 和配置文件中的类名“DelegateSynchronization”。
    // 注意: 为了启动 WCF 测试客户端以测试此服务，请在解决方案资源管理器中选择 DelegateSynchronization.svc 或 DelegateSynchronization.svc.cs，然后开始调试。
    public class DelegateSynchronization : IDelegateSynchronization
    {
        public bool SyncData(DataTable updata, string tableName, bool isCheckColumnExist, out string errormsg)
        {
            DBSession db = new DBSession(DataBaseType.MySql);
            bool isOk = true;
            errormsg = string.Empty;
            string insert_update_sql = string.Empty;
            List<MySqlParameter> lstMySqlPara = new List<MySqlParameter>();
            try
            {
                List<string> lstCols = new List<string>();
                if (isCheckColumnExist)
                {
                    lstCols = GetAllColumnNames(tableName);
                }
                if (updata != null && updata.Rows.Count > 0)
                {
                    //Id 是否自增
                    bool IsIdAuto = false;
                    string sel_sql = string.Format("show columns from  {0} where Field like '%id%' and extra='auto_increment'", tableName);
                    IsIdAuto = db.QueryRow(sel_sql, 300) != null;
                    insert_update_sql = " INSERT INTO " + tableName + " ({0}) VALUES ({1}) on duplicate key update {2};";
                    List<string> lstField = new List<string>();
                    List<string> lstField2 = new List<string>();
                    List<string> lstUpdateField = new List<string>();
                    foreach (DataColumn column in updata.Columns)
                    {
                        if (isCheckColumnExist && !lstCols.Contains(column.ColumnName))
                        {
                            continue;
                        }
                        //if (IsIdAuto && column.ColumnName.ToLower() == "id")
                        //{
                        //    continue;
                        //}
                        if (column.ColumnName.ToLower() == "timestamp")
                        {
                            continue;
                        }
                        lstField.Add(column.ColumnName);
                        lstField2.Add("@" + column.ColumnName);
                        lstUpdateField.Add(column.ColumnName + "=@" + column.ColumnName);
                    }
                    insert_update_sql = string.Format(insert_update_sql, string.Join(",", lstField.ToArray()), string.Join(",", lstField2.ToArray()), string.Join(",", lstUpdateField.ToArray()));
                   
                    for (int r = 0; r < updata.Rows.Count; r++)
                    {
                        DataRow dr = updata.Rows[r];
                        MySqlParameter para = null;
                        for (int c = 0; c < updata.Columns.Count; c++)
                        {
                            DataColumn dc = updata.Columns[c];
                            string columnName = dc.ColumnName;
                            if (isCheckColumnExist && !lstCols.Contains(columnName))
                            {
                                continue;
                            }
                            //if (IsIdAuto && columnName.ToLower() == "id")
                            //{
                            //    continue;
                            //}
                            object value = dr[dc];
                            para = new MySqlParameter("@" + columnName, value);
                            lstMySqlPara.Add(para);
                        }
                        int res = db.ExecuteSql(insert_update_sql, lstMySqlPara.ToArray());
                        lstMySqlPara.Clear();
                    }
                }
            }
            catch (Exception ex)
            {
                LogUtils.LogError("上传数据到MySql出错", Developer.MJ, ex, "当前上传表：" + tableName);
                isOk = false;
                errormsg = ex.Message;
            }
            return isOk;
        }

        public DataTable QueryTable(string SQLString, int Times = 300)
        {
            DBSession db = new DBSession(DataBaseType.MySql);
            return db.QueryTable(SQLString, Times);
        }


        public bool ExecuteSql(string sql)
        {
            try
            {
                DBSession db = new DBSession(DataBaseType.MySql);
                db.ExecuteSql(sql);
                return true;
            }
            catch
            {
                return false;
            }

        }


        public bool DeleteHistoryByKey(string tableName, out string errormsg, string key)
        {
            DBSession db = new DBSession(DataBaseType.MySql);
            bool isOk = true;
            errormsg = string.Empty;
            {
                string delete_sql = "delete from DeleteHistory where  tableName ='" + tableName + "'"
                    + " and `key`=" + key;
                try
                {
                    db.ExecuteSql(delete_sql);
                }
                catch (Exception ex)
                {
                    LogUtils.LogError("MySql删除同步历史出错", Developer.SysDefault, ex, "执行语句:" + delete_sql);
                    isOk = false;
                    errormsg = ex.Message;
                }
            }
            return isOk;
        }

        public bool UpData(DataTable updata, string tableName, string uniqueColName, params string[] excludeColNames)
        {
            DBSession db = new DBSession(DataBaseType.MySql);
            Hashtable htTemp = new Hashtable();
            try
            {
                if (updata != null && updata.Rows.Count > 0)
                {
                    List<Hashtable> lstHt = ConvertHashtable(updata);
                    List<string> lstEldColName = new List<string>();
                    if (excludeColNames != null)
                    {
                        lstEldColName = excludeColNames.ToList();
                    }

                    //if (uniqueColName != "Id")
                    //{
                    //    lstEldColName.Add("Id");
                    //}

                    //固定要排除的列
                    lstEldColName.Add("timestamp");
                    lstEldColName.Add("rowNum");

                    foreach (Hashtable ht in lstHt)
                    {
                        htTemp = ht;
                        string exist_sql = "select count(1) from {0} where {1}='{2}'";
                        exist_sql = string.Format(exist_sql, tableName, uniqueColName, ht[uniqueColName].ToString().ToString());
                        int res = -1;
                        bool isok = false;
                        if (db.Exists(exist_sql))
                        {
                            res = db.Update(ht, tableName, uniqueColName, lstEldColName.ToArray());
                        }
                        else
                        {
                            isok = db.Insert(ht, tableName, lstEldColName.ToArray());
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                LogUtils.LogError("MySql UpData数据同步出错", Developer.MJ, ex, tableName, htTemp);
                return false;
            }
        }

        private List<Hashtable> ConvertHashtable(DataTable data)
        {
            List<Hashtable> lstHt = new List<Hashtable>();
            foreach (DataRow dr in data.Rows)
            {
                Hashtable ht = new Hashtable(StringComparer.OrdinalIgnoreCase);
                foreach (DataColumn dc in data.Columns)
                {
                    object value = dr[dc.ColumnName];
                    ht.Add(dc.ColumnName, value);
                }
                lstHt.Add(ht);
            }
            return lstHt;
        }

        public static List<string> GetAllColumnNames(string tableName)
        {
            DBSession db = new DBSession(DataBaseType.MySql);
            List<string> lstCols = new List<string>();
            string sel_sql = "SELECT Name FROM SysColumns WHERE id=Object_Id('{0}') ";
            sel_sql = "show columns from {0} ";
            sel_sql = string.Format(sel_sql, tableName.Trim());
            lstCols = db.QueryTable(sel_sql, 300).GetFirstCol<string>();
            return lstCols;
        }
    }
}
